﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using JKF.Substructure;

namespace JKF.DataModule.SqlServerDAL
{
    public partial class dalSTOCK_OUT:IDAL.IdalSTOCK_OUT
    {
        private const string FieldShow = "PRODUCT_KEY, NAME, MODEL, TYPE, SORT_NAME, STATUS, DATE_OUT, PRODUCT_NUMS, OUT_NO, MEMO, OUT_KEY, USER_CREATE, SHOP_CODE, DATE_CREATE";
        private const string FieldOrder = "DATE_OUT DESC,NAME,OUT_NO";
        private const string FieldKey = "OUT_KEY";
        /// <summary>
        ///  从视图 dbo.V_PRODUCT_STOCK_OUT 获取出库时数据列表
        /// </summary>
        /// <param name="shop_code">当前用户所属店面</param>
        /// <param name="NAME">产品名称</param>
        /// <param name="TYPE">产品类别</param>
        /// <param name="PRODUCT_NUMS">出库数量</param>
        /// <param name="IN_NO">出库单号</param>
        /// <param name="REAL_NAME">操作员姓名</param>
        /// <param name="MEMO">备注</param>
        /// <param name="DATE_START">出库开始日期</param>
        /// <param name="DATE_END">出库结束日期</param>
        /// <param name="pageCurrent">当前页码</param>
        /// <param name="pageSize">一页显示多少条数据</param>
        /// <param name="pageCount">总页数</param>
        /// <param name="rowCount">总记录数</param>
        /// <returns>返回DataTable数据集</returns>
        public static DataTable GetStock_OutsDAL(string NAME,string shop_code, string product_key, string PRODUCT_NUMS, string OUT_NO, string REAL_NAME, string MEMO, string DATE_START, string DATE_END, int pageCurrent, int pageSize, ref int pageCount, ref int rowCount)
        {
            //获取有效的出库记录 1.表示有效，9表示删除
            var sql = new StringBuilder(string.Format("  shop_code='{0}' and status='{1}' ", shop_code, 1));
            if (!string.IsNullOrEmpty(NAME))
            {
                sql.AppendFormat(" AND NAME  LIKE '%{0}%'", NAME);
            }
            //if (!string.IsNullOrEmpty(TYPE))
            //{
            //    sql.AppendFormat(" AND TYPE= '{0}'", TYPE);
            //}
            if (!string.IsNullOrEmpty(product_key))
            {
                sql.AppendFormat(@" AND PRODUCT_KEY IN (select [value] 
from [dbo].[Split_Product_Key]('{0}',','))", product_key);
            }
            if (!string.IsNullOrEmpty(PRODUCT_NUMS))
            {
                sql.AppendFormat(" AND PRODUCT_NUMS ={0}", PRODUCT_NUMS);
            }
            if (!string.IsNullOrEmpty(OUT_NO))
            {
                sql.AppendFormat(" AND OUT_NO LIKE '%{0}%'", OUT_NO);
            }
            if (!string.IsNullOrEmpty(REAL_NAME))
            { //视图中 USER_CREATE 为操作员姓名
                sql.AppendFormat(" AND USER_CREATE LIKE '%{0}%'", REAL_NAME);
            }
            if (!string.IsNullOrEmpty(MEMO))
            {
                sql.AppendFormat(" AND MEMO LIKE '%{0}%'", MEMO);
            }
            if (!string.IsNullOrEmpty(DATE_START))
            {
                sql.AppendFormat(" and DATE_OUT >='{0}'", DATE_START);
            }
            if (!string.IsNullOrEmpty(DATE_END))
            {
                sql.AppendFormat(" and DATE_OUT <='{0}'", DATE_END);
            }

            return DataPage.GetPageInfo("dbo.V_PRODUCT_STOCK_OUT", FieldKey, pageCurrent, pageSize, FieldShow, FieldOrder, sql.ToString(), ref pageCount, ref rowCount).Tables[0];
        }
        /// <summary>
        /// 事务执行SQL语句
        /// </summary>
        /// <returns></returns>
        public static bool TransationSql(string sql)
        {
            //采用事务提交
            using (TransactionHR tHR = DB.BeginTransactionHR())
            {
                try
                {
                    int result = tHR.ExecuteNonQuery(sql);

                    if (result > 0)
                    {
                        tHR.Commit();
                        return true;
                    }
                    else
                    {
                        tHR.Rollback();
                        return false;
                    }
                }
                catch (Exception ex)
                {
                    tHR.Rollback();
                    //return false;
                    throw ex;

                }
            }
        }
        /// <summary>
        /// 根据当前店铺编码获取其入库单号
        /// </summary>
        /// <param name="shop_code"></param>
        /// <returns></returns>
        public static string GetStock_OUT_NODAL(string shop_code)
        {
            string sql = "SELECT [dbo].[fn_get_stock_out_no]('" + shop_code + "')";
            return DB.HR.ExecuteScalar(CommandType.Text, sql).ToString();
        }
    }
}
